package com.timeDeal.service;


import com.timeDeal.bean.PageList;
import com.timeDeal.bean.ShijianchushouBean;
import com.timeDeal.bean.XuqiufabuBean;
import com.timeDeal.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 妮子 on 2016/9/12.
 */
public class WofabudeService {
    public PageList getXuqiuList(int jiafangid,int pagesize,int currentpage){
        Connection conn=DButil.getConnection();
        String sqla="SELECT * FROM xuqiufabu WHERE jiafangid=? AND zhuangtai='审核通过'";
        String sqlb="ORDER BY xuqiufabuid ASC";
        String sql=sqla +" "+ sqlb+" limit " +((currentpage-1)*pagesize)+","+pagesize;
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        PreparedStatement ps2=null;
        ResultSet rs=null;
        ResultSet rs2=null;
        PageList result=null;
        try{
            try {
                result=new PageList();
                result.setCurrentpage(currentpage);
                String sqlc="select count(*),ceil(count(*)/"+pagesize+") from ("+sqla+") as a";
                ps2=conn.prepareStatement(sqlc);
                ps2.setInt(1,jiafangid);
                rs2=ps2.executeQuery();
                if(rs2.next()){
                    result.setTotalrow(rs2.getInt(1));
                    result.setTotalpage(rs2.getInt(2));
                }else{
                    result.setTotalrow(0);
                }
                List<Object> list=new ArrayList<>();
                ps.setInt(1,jiafangid);
                rs=ps.executeQuery();
                while (rs.next()){
                    XuqiufabuBean xuqiufabuBean=new XuqiufabuBean();
                    xuqiufabuBean.setXuqiumingcheng(rs.getString("mingcheng"));
                    xuqiufabuBean.setYunxujingbiao(rs.getString("yunxujingbiao"));
                    xuqiufabuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                    list.add(xuqiufabuBean);
                }
                result.setList(list);
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }finally {
            try {
                rs.close();
                rs2.close();
               ps.close();
                ps2.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }
    public PageList getShijianList(int jiafangid,int pagesize,int currentpage){
        Connection conn=DButil.getConnection();
        String sqla="SELECT * FROM shijianchushou WHERE jiafangid=? AND zhuangtai='审核通过'";
        String sqlb="ORDER BY shijianchushouid ASC";
        String sql=sqla +" "+ sqlb+" limit " +((currentpage-1)*pagesize)+","+pagesize;
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        PreparedStatement ps2=null;
        ResultSet rs=null;
        ResultSet rs2=null;
        PageList result=null;
        try{
            try {
                result=new PageList();
                result.setCurrentpage(currentpage);
                String sqlc="select count(*),ceil(count(*)/"+pagesize+") from ("+sqla+") as a";
                ps2=conn.prepareStatement(sqlc);
                ps2.setInt(1,jiafangid);
                rs2=ps2.executeQuery();
                if(rs2.next()){
                    result.setTotalrow(rs2.getInt(1));
                    result.setTotalpage(rs2.getInt(2));
                }else{
                    result.setTotalrow(0);
                }
                List<Object> list=new ArrayList<>();
                ps.setInt(1,jiafangid);
                rs=ps.executeQuery();
                while (rs.next()){
                    ShijianchushouBean shijianchushouBean=new ShijianchushouBean();
                    shijianchushouBean.setShijianchushoumingcheng(rs.getString("mingcheng"));
                    shijianchushouBean.setShijianchushouid(rs.getInt("shijianchushouid"));
                    shijianchushouBean.setYunxujingbiao(rs.getString("yunxujingbiao"));
                    list.add(shijianchushouBean);
                }
                result.setList(list);
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }finally {
            try {
                rs.close();
                rs2.close();
                ps.close();
                ps2.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }
}
